﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
using Microsoft.Practices.EnterpriseLibrary.Data;

namespace Model
{
    public class DBUtils
    {
        private SqlConnection sqlCnn = new SqlConnection(Session.DBConnectionString);

        public DBUtils()
        {
            sqlCnn.Open();
        }

        public void CloseDBUtils()
        {
            try
            {
                SqlConnection.ClearPool(sqlCnn);
            }
            catch
            {
            }

            try
            {
                sqlCnn.Dispose();
            }
            catch
            {
            }
        }

        public SqlDataReader QueryWithResult(string command)
        {
            if (sqlCnn.State == ConnectionState.Open)
                sqlCnn.Close();
            sqlCnn.Open();
            SqlCommand cmd = new SqlCommand(command, sqlCnn);
            SqlDataReader dataReader = null;
            try
            {
                dataReader = cmd.ExecuteReader();
            }
            catch (Exception)
            {
                throw;
            }
            cmd.Dispose();
            return dataReader;
        }

        public void QueryWithNoResult(string command)
        {
            try
            {
                if (sqlCnn.State == ConnectionState.Open)
                    sqlCnn.Close();
                sqlCnn.Open();
                SqlCommand cmd = new SqlCommand(command, sqlCnn);
                cmd.ExecuteNonQuery();
                cmd.Dispose();
            }
            catch (Exception)
            {
                throw;
            }
        }

        public DataTable QueryGetDataTable(string command)
        {
            DataTable db = new DataTable();
            try
            {
                if (sqlCnn.State == ConnectionState.Open)
                    sqlCnn.Close();
                sqlCnn.Open();
                SqlCommand cmd = new SqlCommand(command, sqlCnn);
                SqlDataAdapter adapter = new SqlDataAdapter();
                adapter.SelectCommand = cmd;
                adapter.Fill(db);
                adapter.Dispose();

                cmd.Dispose();
            }
            catch (Exception)
            {
                throw;
            }
            return db;
        }
    }
}
